using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using UMEVOR.DTO;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UMEVOR.DAL
{
    public class DALT_EVANGELIZACAO : DALConnection
    {

        #region Incluir

        public void incluir(T_EVANGELIZACAO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" INSERT INTO T_EVANGELIZACAO ");
                SQL.Append("    (ID_AUTOR, TX_RESUMO, TX_FONTE, TX_EMAIL, DT_POST, TX_TITULO) ");
                SQL.Append(" VALUES ");
                SQL.Append("    ("+ obj.ID_AUTOR.ToString() + ", '"+ obj.TX_RESUMO +"',");
                SQL.Append("'" + obj.TX_FONTE + "', '" + obj.TX_EMAIL + "', str_to_date('" + obj.DT_POST.ToString("dd/MM/yyyy") + "', '%d/%m/%Y'),");
                SQL.Append("'" + obj.TX_TITULO + "') ");

                DbCommand insertT_EVANGELIZACAO = database.GetSqlStringCommand(SQL.ToString());

                insertT_EVANGELIZACAO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(insertT_EVANGELIZACAO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Incluir[]

        public void incluir(T_EVANGELIZACAO[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] insertT_EVANGELIZACAO = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_EVANGELIZACAO o = obj[index];
                    SQL = new StringBuilder();

                    SQL.Append(" INSERT INTO T_EVANGELIZACAO ");
                    SQL.Append("    (ID_EVANGELIZACAO, ID_AUTOR, TX_RESUMO, TX_FONTE, TX_EMAIL, DT_POST, TX_TITULO) ");
                    SQL.Append(" VALUES ");
                    SQL.Append("    (:ID_EVANGELIZACAO, :ID_AUTOR, :TX_RESUMO, :TX_FONTE, :TX_EMAIL, :DT_POST, :TX_TITULO) ");

                    insertT_EVANGELIZACAO[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(insertT_EVANGELIZACAO[index], ":ID_EVANGELIZACAO", DbType.Int64, o.ID_EVANGELIZACAO);
                    database.AddInParameter(insertT_EVANGELIZACAO[index], ":ID_AUTOR", DbType.Int64, o.ID_AUTOR);
                    database.AddInParameter(insertT_EVANGELIZACAO[index], ":TX_RESUMO", DbType.String, o.TX_RESUMO);
                    database.AddInParameter(insertT_EVANGELIZACAO[index], ":TX_FONTE", DbType.String, o.TX_FONTE);
                    database.AddInParameter(insertT_EVANGELIZACAO[index], ":TX_EMAIL", DbType.String, o.TX_EMAIL);
                    database.AddInParameter(insertT_EVANGELIZACAO[index], ":DT_POST", DbType.Object, o.DT_POST);
                    database.AddInParameter(insertT_EVANGELIZACAO[index], ":TX_TITULO", DbType.String, o.TX_TITULO);

                    insertT_EVANGELIZACAO[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in insertT_EVANGELIZACAO)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar

        public void alterar(T_EVANGELIZACAO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" UPDATE T_EVANGELIZACAO SET ");
                SQL.Append("    ID_AUTOR = " + obj.ID_AUTOR.ToString() + " , ");
                SQL.Append("    TX_RESUMO = '" + obj.TX_RESUMO + "' , ");
                SQL.Append("    TX_FONTE = '" + obj.TX_FONTE + "' , ");
                SQL.Append("    TX_EMAIL = '" + obj.TX_EMAIL + "' , ");
                SQL.Append("    DT_POST = str_to_date('" + obj.DT_POST.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') , ");
                SQL.Append("    TX_TITULO = '" + obj.TX_TITULO + "' ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_EVANGELIZACAO = " + obj.ID_EVANGELIZACAO.ToString());

                DbCommand updateT_EVANGELIZACAO = database.GetSqlStringCommand(SQL.ToString());

                updateT_EVANGELIZACAO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(updateT_EVANGELIZACAO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar[]

        public void alterar(T_EVANGELIZACAO[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] updateT_EVANGELIZACAO = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_EVANGELIZACAO o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" UPDATE T_EVANGELIZACAO SET ");
                    SQL.Append("    ID_AUTOR = :ID_AUTOR , ");
                    SQL.Append("    TX_RESUMO = :TX_RESUMO , ");
                    SQL.Append("    TX_FONTE = :TX_FONTE , ");
                    SQL.Append("    TX_EMAIL = :TX_EMAIL , ");
                    SQL.Append("    DT_POST = :DT_POST , ");
                    SQL.Append("    TX_TITULO = :TX_TITULO ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_EVANGELIZACAO = :ID_EVANGELIZACAO  ");

                    updateT_EVANGELIZACAO[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(updateT_EVANGELIZACAO[index], ":ID_EVANGELIZACAO", DbType.Int64, o.ID_EVANGELIZACAO);
                    database.AddInParameter(updateT_EVANGELIZACAO[index], ":ID_AUTOR", DbType.Int64, o.ID_AUTOR);
                    database.AddInParameter(updateT_EVANGELIZACAO[index], ":TX_RESUMO", DbType.String, o.TX_RESUMO);
                    database.AddInParameter(updateT_EVANGELIZACAO[index], ":TX_FONTE", DbType.String, o.TX_FONTE);
                    database.AddInParameter(updateT_EVANGELIZACAO[index], ":TX_EMAIL", DbType.String, o.TX_EMAIL);
                    database.AddInParameter(updateT_EVANGELIZACAO[index], ":DT_POST", DbType.Object, o.DT_POST);
                    database.AddInParameter(updateT_EVANGELIZACAO[index], ":TX_TITULO", DbType.String, o.TX_TITULO);

                    updateT_EVANGELIZACAO[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in updateT_EVANGELIZACAO)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir

        public void excluir(T_EVANGELIZACAO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" DELETE FROM T_EVANGELIZACAO ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_EVANGELIZACAO = " + obj.ID_EVANGELIZACAO.ToString());

                DbCommand deleteT_EVANGELIZACAO = database.GetSqlStringCommand(SQL.ToString());

                deleteT_EVANGELIZACAO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(deleteT_EVANGELIZACAO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir[]

        public void excluir(T_EVANGELIZACAO[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] deleteT_EVANGELIZACAO = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_EVANGELIZACAO o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" DELETE FROM T_EVANGELIZACAO ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_EVANGELIZACAO = :ID_EVANGELIZACAO  ");

                    deleteT_EVANGELIZACAO[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(deleteT_EVANGELIZACAO[index], ":ID_EVANGELIZACAO", DbType.Int64, o.ID_EVANGELIZACAO);

                    deleteT_EVANGELIZACAO[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in deleteT_EVANGELIZACAO)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ObterPorPK

        public T_EVANGELIZACAO obterPorPK(long pID_EVANGELIZACAO)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_EVANGELIZACAO ");
                SQL.Append(" WHERE ");
                SQL.Append("  ID_EVANGELIZACAO = " + pID_EVANGELIZACAO.ToString());

                DbCommand consultaT_EVANGELIZACAO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_EVANGELIZACAO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_EVANGELIZACAO, DALConnection.transaction);
                T_EVANGELIZACAO obj = null;

                if (reader.Read())
                {
                    obj = new T_EVANGELIZACAO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_EVANGELIZACAO")))
                    {
                        obj.ID_EVANGELIZACAO = Int64.Parse(reader["ID_EVANGELIZACAO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR")))
                    {
                        obj.ID_AUTOR = Int64.Parse(reader["ID_AUTOR"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_RESUMO")))
                    {
                        obj.TX_RESUMO = reader["TX_RESUMO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_EMAIL")))
                    {
                        obj.TX_EMAIL = reader["TX_EMAIL"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_POST")))
                    {
                        obj.DT_POST = DateTime.Parse(reader["DT_POST"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                }
                reader.Dispose();
                consultaT_EVANGELIZACAO.Dispose();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ListarTodos

        public List<T_EVANGELIZACAO> listarTodos()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_EVANGELIZACAO ORDER BY TX_RESUMO");

                DbCommand consultaT_EVANGELIZACAO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_EVANGELIZACAO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_EVANGELIZACAO, DALConnection.transaction);
                List<T_EVANGELIZACAO> lista = new List<T_EVANGELIZACAO>();
                T_EVANGELIZACAO obj = null;

                while (reader.Read())
                {
                    obj = new T_EVANGELIZACAO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_EVANGELIZACAO")))
                    {
                        obj.ID_EVANGELIZACAO = Int64.Parse(reader["ID_EVANGELIZACAO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR")))
                    {
                        obj.ID_AUTOR = Int64.Parse(reader["ID_AUTOR"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_RESUMO")))
                    {
                        obj.TX_RESUMO = reader["TX_RESUMO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_EMAIL")))
                    {
                        obj.TX_EMAIL = reader["TX_EMAIL"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_POST")))
                    {
                        obj.DT_POST = DateTime.Parse(reader["DT_ULT_ACESSO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_EVANGELIZACAO.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<VOT_EVANGELIZACAO> listarTodosPortal()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT E.ID_EVANGELIZACAO, E.ID_AUTOR, E.TX_RESUMO, E.TX_FONTE, E.TX_EMAIL, E.DT_POST, E.TX_TITULO, A.TX_NOME ");
                SQL.Append("FROM T_EVANGELIZACAO E, T_AUTOR A WHERE A.ID_AUTOR = E.ID_AUTOR ORDER BY E.DT_POST DESC");

                DbCommand consultaT_EVANGELIZACAO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_EVANGELIZACAO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_EVANGELIZACAO, DALConnection.transaction);
                List<VOT_EVANGELIZACAO> lista = new List<VOT_EVANGELIZACAO>();
                VOT_EVANGELIZACAO obj = null;

                while (reader.Read())
                {
                    obj = new VOT_EVANGELIZACAO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_EVANGELIZACAO")))
                    {
                        obj.ID_EVANGELIZACAO = Int64.Parse(reader["ID_EVANGELIZACAO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR")))
                    {
                        obj.ID_AUTOR = Int64.Parse(reader["ID_AUTOR"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_RESUMO")))
                    {
                        obj.TX_RESUMO = reader["TX_RESUMO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME")))
                    {
                        obj.TX_AUTOR = reader["TX_NOME"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_EMAIL")))
                    {
                        obj.TX_EMAIL = reader["TX_EMAIL"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_POST")))
                    {
                        obj.DT_POST = DateTime.Parse(reader["DT_POST"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_EVANGELIZACAO.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<VOT_EVANGELIZACAO> consultar(string titulo, int id_autor, DateTime data)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT T.ID_EVANGELIZACAO, T.ID_AUTOR, T.TX_RESUMO, T.TX_FONTE, T.TX_EMAIL, T.DT_POST, T.TX_TITULO,A.TX_NOME ");
                SQL.Append("FROM T_EVANGELIZACAO T, T_AUTOR A WHERE A.ID_AUTOR = T.ID_AUTOR");

                if (!titulo.Equals(""))
                    SQL.Append("  AND T.TX_TITULO like '" + titulo + "%'");

                if (!id_autor.Equals(0))
                    SQL.Append(" AND T.ID_AUTOR = " + id_autor);

                if (data.ToString("dd/MM/yyyy") != "01/01/0001")
                    SQL.Append(" AND T.DT_POST = str_to_date('" + data.ToString() + "', '%d/%m/%Y')");

                SQL.Append(" ORDER BY T.DT_POST DESC, A.ID_AUTOR ASC, T.TX_TITULO ASC");

                DbCommand consultaT_ARTIGO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_ARTIGO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_ARTIGO, DALConnection.transaction);
                List<VOT_EVANGELIZACAO> lista = new List<VOT_EVANGELIZACAO>();
                VOT_EVANGELIZACAO obj = null;

                while (reader.Read())
                {
                    obj = new VOT_EVANGELIZACAO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_EVANGELIZACAO")))
                    {
                        obj.ID_EVANGELIZACAO = Int64.Parse(reader["ID_EVANGELIZACAO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR")))
                    {
                        obj.ID_AUTOR = Int64.Parse(reader["ID_AUTOR"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_RESUMO")))
                    {
                        obj.TX_RESUMO = reader["TX_RESUMO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_EMAIL")))
                    {
                        obj.TX_EMAIL = reader["TX_EMAIL"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_POST")))
                    {
                        obj.DT_POST = DateTime.Parse(reader["DT_POST"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME")))
                    {
                        obj.TX_AUTOR = reader["TX_NOME"].ToString();
                    }
                    lista.Add(obj);

                }
                reader.Dispose();
                consultaT_ARTIGO.Dispose();
                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}

